<?php

class Default_Model_Upgrade_StudSemeEduh extends Default_Model_Upgrade_Core
{
    protected $_table= 'stud_seme_eduh';

    protected $_targetTable = array('student_guidance');

    public function upgrade() {
        if ($this->isUpgrade()) {
            return 'isUpgrde';
        } else {
            $hasData = 0 ;
            // 先判斷是否有資料
            $query = "SELECT COUNT(*)  AS cc FROM student_guidance";
            $res = $this->_newdbPdo->query($query)->fetch();
            $hasData = $res['cc'];

            $query="ALTER TABLE `student_guidance`
             DROP FOREIGN KEY `student_guidance_ibfk_1`";
            $this->_newdbPdo->exec($query);

            $query = "ALTER TABLE `student_guidance`  ENGINE = MYISAM";
            $this->_newdbPdo->exec($query);

            $query = "SELECT a.*,b.student_sn FROM stud_seme_eduh a , stud_base b WHERE a.stud_id=b.stud_id";
            $res = $this->_sfs3Pdo->query($query)->fetchAll(PDO::FETCH_ASSOC);
            foreach($res as $row) {
                $arr = array();
                $arr['student_sn'] = $row['student_sn'];
                $arr['year'] = (int)substr($row['seme_year_seme'],0,-1);
                $arr['semester'] = (int)substr($row['seme_year_seme'],-1);
                if ($hasData) {
                    $find = $this->_newdbPdo->query("SELECT student_sn FROM student_guidance
				  WHERE  student_sn ='{$arr['student_sn']}'
				  AND year ='{$arr['year']}'
				  AND semester ='{$arr['semester']}'
				  ");
                    if ($find->rowCount() >0)
                    continue;
                }
                $arr['parent_relation'] = $row['sse_relation'];
                $arr['family_kind'] = $row['sse_family_kind'];
                $arr['family_air'] = $row['sse_family_air'];
                $arr['discipline_father'] = $row['sse_farther'];
                $arr['discipline_mother'] = $row['sse_mother'];
                $arr['live_state'] = $row['sse_live_state'];
                $arr['asset_state'] = $row['sse_rich_state'];
                $arr['favorite_scopes'] = $row['sse_s1'];
                $arr['difficult_scpoes'] = $row['sse_s2'];
                $arr['particular_ability'] = $row['sse_s3'];
                $arr['interest'] = $row['sse_s4'];
                $arr['living_habit'] = $row['sse_s5'];
                $arr['relationship'] = $row['sse_s6'];
                $arr['behavior_extrovert'] = $row['sse_s7'];
                $arr['behavior_introvert'] = $row['sse_s8'];
                $arr['behavior_learning'] = $row['sse_s9'];
                $arr['bad_manner'] = $row['sse_s10'];
                $arr['behavior_anxiety'] = $row['sse_s11'];
                $this->fromArray('student_guidance',$arr);
            }

            $query = "ALTER TABLE `student_guidance`  ENGINE = INNODB";
            $this->_newdbPdo->exec($query);
            $query="ALTER TABLE `student_guidance`
  ADD CONSTRAINT `student_guidance_ibfk_1` FOREIGN KEY (`student_sn`) REFERENCES `student` (`sn`) ON UPDATE CASCADE;
            ";
            $this->_newdbPdo->exec($query);

            $log['memo'] = '學期輔導記錄';
            $log['upgrade_time']	= date("Y-m-d h:i:s");
            $this->finishUpgrade($log);
        }
    }
}